package com.rabbiter.ordering.mapper;



import com.rabbiter.ordering.entity.CategorySalesDTO;
import com.rabbiter.ordering.entity.FullReportDTO;
import com.rabbiter.ordering.entity.MonthlySalesDTO;
import com.rabbiter.ordering.entity.OrderItem;
import org.apache.ibatis.annotations.*;

import java.util.Date;
import java.util.List;

@Mapper
public interface OrderItemMapper {
    @Insert("INSERT INTO order_item(name, price, quantity, is_package, cateName, flavor,order_id,tableId) " +
            "VALUES(#{name}, #{price}, #{quantity}, #{isPackage}, #{cateName}, #{flavor},#{orderId},#{tableId})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(OrderItem orderItem);

    @Select("SELECT * FROM order_item WHERE id = #{id}")
    OrderItem selectById(Long id);

    @Select("SELECT * FROM order_item")
    List<OrderItem> selectAll();

    @Update("UPDATE order_item SET status = #{status} WHERE id=#{id}")
    int update(OrderItem orderItem);

    @Delete("DELETE FROM order_item WHERE id=#{id}")
    int delete(Long id);

    // 根据订单ID查询订单项
    @Select("SELECT * FROM order_item WHERE order_id = #{orderId}")
    List<OrderItem> selectByOrderId(Long orderId);


        @Select("SELECT cateName AS category, SUM(price * quantity) AS totalSales, " +
                "COUNT(DISTINCT o.order_no) AS orderCount " +
                "FROM order_item oi JOIN orders o ON oi.order_id = o.order_no " +
                "WHERE o.time BETWEEN #{startDate} AND #{endDate} " +
                "GROUP BY cateName ORDER BY totalSales DESC")
        List<CategorySalesDTO> selectSalesByCategory(@Param("startDate") Date startDate,
                                                     @Param("endDate") Date endDate);

        @Select("SELECT DATE_FORMAT(o.time, '%Y-%m') AS month, " +
                "SUM(oi.price * oi.quantity) AS totalSales, " +
                "COUNT(DISTINCT o.order_no) AS orderCount " +
                "FROM order_item oi JOIN orders o ON oi.order_id = o.order_no " +
                "WHERE o.time BETWEEN #{startDate} AND #{endDate} " +
                "GROUP BY DATE_FORMAT(o.time, '%Y-%m') ORDER BY month")
        List<MonthlySalesDTO> selectSalesByMonth(@Param("startDate") Date startDate,
                                                 @Param("endDate") Date endDate);

        @Select("<script>" +
                "SELECT DATE_FORMAT(o.time, '%Y-%m') AS month, oi.cateName AS category, " +
                "SUM(oi.price * oi.quantity) AS totalSales, " +
                "COUNT(DISTINCT o.order_no) AS orderCount " +
                "FROM order_item oi JOIN orders o ON oi.order_id = o.order_no " +
                "WHERE o.time BETWEEN #{startDate} AND #{endDate} " +
                "<if test='status != null'> </if>" +
                "GROUP BY DATE_FORMAT(o.time, '%Y-%m'), oi.cateName ORDER BY month, totalSales DESC" +
                "</script>")
        List<FullReportDTO> selectFullReport(@Param("startDate") Date startDate,
                                             @Param("endDate") Date endDate,
                                             @Param("status") String status);

}
